package com.feidee.data.report.dao.impl;

import com.feidee.data.report.constant.Constant;
import com.feidee.data.report.dao.ICustomReportDao;
import com.feidee.data.report.model.BuildResultTblModel;
import com.feidee.data.report.model.ColumnInfoModel;
import com.feidee.data.report.model.CustomReportModel;
import com.feidee.data.report.util.DbOperationUtil;
import com.feidee.data.report.util.ResultResponseUtil;
import com.feidee.data.report.util.ToolUtils;
import com.feidee.fdcommon.configuration.CustomConfiguration;
import org.apache.log4j.Logger;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Map;

public class CustomReportDaoImpl implements ICustomReportDao {
    private static Logger logger = Logger.getLogger(CustomReportDaoImpl.class);
    public DataSource confDataSource;
    public DataSource cusMysqlDataSource;

    public CustomReportDaoImpl() {
    }

    /**
     * 创建报表结果存储表
     *
     * @param crm
     * @return
     */
    public int createReportMysqlTbl(final CustomReportModel crm) {
        Connection mysqlConnCre = null;
        PreparedStatement statement = null;
        Map<String, ColumnInfoModel> mysqlColumn = crm.getMysqlColumn();
        try {
            mysqlConnCre = cusMysqlDataSource.getConnection();
            logger.info("正在尝试使用Druid连接池...");
            // 删除原表和视图
            String mysqlDropViewStr = "DROP VIEW IF EXISTS `" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + crm.getApplysqlname() + "`";
            String mysqlDropTblStr = "DROP TABLE IF EXISTS `" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + crm.getApplysqlname() + "`";
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 删除Mysql视图: " + mysqlDropViewStr);
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 删除Mysql结果表: " + mysqlDropTblStr);
            statement = mysqlConnCre.prepareStatement(mysqlDropViewStr);
            statement.execute(mysqlDropViewStr);
            statement = mysqlConnCre.prepareStatement(mysqlDropTblStr);
            statement.execute(mysqlDropTblStr);
            // 创建新表
            String mysqlStrCre = "CREATE TABLE `" + Constant.CUSTOM_REPORT_MYSQL_TABLE_PREFIX + crm.getApplysqlname()
                    + "`( `id` bigint NULL AUTO_INCREMENT ,`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP comment '创建时间'," +
                    "`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间',";
            String keys = null;
            String indexKeys = null;
            for (String key : mysqlColumn.keySet()) {
                // 拼接主键
                ColumnInfoModel columnInfoModel = mysqlColumn.get(key);
                if (columnInfoModel.getDimType().equals("1")) {
                    if (keys != null) {
                        if ("ymd".equals(key)) {
                            keys = "`" + key + "`," + keys;
                        } else {
                            keys = keys + ",`" + key + "`";
                        }
                    } else {
                        keys = "`" + key + "`";
                    }
                }
                if (columnInfoModel.getDimType().equals("3")) {
                    if (indexKeys != null) {
                        indexKeys = indexKeys + ",`" + key + "`";
                    } else {
                        indexKeys = "`" + key + "`";
                    }
                }
                String columnType = columnInfoModel.getColumnType();
                if (columnType.toLowerCase().contains("varchar")) {
                    mysqlStrCre += "`" + key + "`" + " " + columnInfoModel.getColumnType() + " DEFAULT '' COMMENT '"
                            + columnInfoModel.getColumnValue() + "',";
                } else {
                    mysqlStrCre += "`" + key + "`" + " " + columnInfoModel.getColumnType() + " DEFAULT '0' COMMENT '"
                            + columnInfoModel.getColumnValue() + "',";
                }
            }
            // 如果报表存在主键则拼接主键,如果不存在则去掉前面多拼接的逗号
            if (ToolUtils.isNotNull(keys)) {
                mysqlStrCre += "PRIMARY KEY (`id`),UNIQUE INDEX `Index_ymd_part_cid` (" + keys + ") USING BTREE";
            } else {
                mysqlStrCre += "PRIMARY KEY (`id`)";
            }
            if (ToolUtils.isNotNull(indexKeys)) {
                mysqlStrCre += ",INDEX `No_unique_index` (" + indexKeys + ") USING BTREE";
            }
            mysqlStrCre += ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ";
            mysqlStrCre += "COMMENT='" + crm.getApplyname() + "'";

            statement.close();
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 创建Mysql结果表: " + mysqlStrCre);
            statement = mysqlConnCre.prepareStatement(mysqlStrCre);
            statement.execute(mysqlStrCre);

            return 0;
        } catch (Exception e) {
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 创建结果表失败,", e);
            ResultResponseUtil.resultResponse(CustomConfiguration.getString("returnurl", Constant.TASK_RES_RETURN_URI),
                    String.valueOf(crm.getId()), "3", "创建报表结果表失败," + e.getMessage());
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(mysqlConnCre, statement);
            logger.info("连接池连接正常被回收...");
        }
    }

    /**
     * 创建到正式表的视图
     *
     * @param crm
     */
    @Override
    public int createViewRefFormal(CustomReportModel crm) {
        Connection reportRsConn = null;
        PreparedStatement createViewStatement = null;
        PreparedStatement queryTableStatement = null;
        ResultSet queryRs = null;
        String createViewStr = "CREATE OR REPLACE VIEW `" + Constant.CUSTOM_VIEW_MYSQL_TABLE_PREFIX + crm.getApplysqlname()
                + "` AS SELECT * FROM `" + crm.getApplysqlname() + "`";
        String queryTable = "SHOW TABLES LIKE '" + Constant.CUSTOM_VIEW_MYSQL_TABLE_PREFIX + crm.getApplysqlname() + "'";
        try {
            reportRsConn = DbOperationUtil.getMysqlConntion(Constant.CUSTOM_REPORT_FLAG);
            queryTableStatement = reportRsConn.prepareStatement(queryTable);
            queryRs = queryTableStatement.executeQuery(queryTable);
            if (queryRs.next()) {
                logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 临时表存在 "
                        + crm.getId() + " 无需创建视图!");
                return 0;
            }
            createViewStatement = reportRsConn.prepareStatement(createViewStr);
            createViewStatement.execute();
            logger.info("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 创建正式表 "
                    + crm.getId() + " 视图成功!,创建视图 SQL:" + createViewStr + " 成功!");
            return 0;
        } catch (Exception e) {
            logger.error("自定义报表 " + crm.getId() + " => " + crm.getApplysqlname() + " 创建正式表 " + crm.getId()
                    + " 视图失败,SQL:" + createViewStr + ",", e);
            ResultResponseUtil.resultResponse(CustomConfiguration.getString("returnurl", Constant.TASK_RES_RETURN_URI), crm.getId(),
                    "3", "创建视图 " + Constant.CUSTOM_VIEW_MYSQL_TABLE_PREFIX + crm.getApplysqlname()
                            + " 失败," + e.getMessage());
            return -1;
        } finally {
            DbOperationUtil.cleanConnEnv(reportRsConn, createViewStatement);
            DbOperationUtil.cleanConnEnv(queryRs);
            DbOperationUtil.cleanConnEnv(queryTableStatement);
        }
    }

    /**
     * 创建报表结果存储表
     *
     * @param crm
     * @return
     */
    public String createResultMysqlTbl(final BuildResultTblModel crm) {
        Connection mysqlConnCre = null;
        PreparedStatement statement = null;
        Map<String, ColumnInfoModel> mysqlColumn = crm.getMysqlColumn();
        try {
            mysqlConnCre = cusMysqlDataSource.getConnection();
            // 删除原表和视图
            String mysqlDropViewStr = "DROP VIEW IF EXISTS `" + crm.getApplysqlname() + "`";
            String mysqlDropTblStr = "DROP TABLE IF EXISTS `" + crm.getApplysqlname() + "`";
            logger.info("创建结果表 " + crm.getApplysqlname() + " 删除Mysql视图: " + mysqlDropViewStr);
            logger.info("创建结果表 " + crm.getApplysqlname() + " 删除Mysql结果表: " + mysqlDropTblStr);
            statement = mysqlConnCre.prepareStatement(mysqlDropViewStr);
            statement.execute(mysqlDropViewStr);
            statement = mysqlConnCre.prepareStatement(mysqlDropTblStr);
            statement.execute(mysqlDropTblStr);
            // 创建新表
            String mysqlStrCre = "CREATE TABLE `" + crm.getApplysqlname()
                    + "`( `id` bigint NULL AUTO_INCREMENT ,`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP comment '创建时间'," +
                    "`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间',";
            String keys = null;
            String indexKeys = null;
            for (String key : mysqlColumn.keySet()) {
                // 拼接主键
                ColumnInfoModel columnInfoModel = mysqlColumn.get(key);
                if (columnInfoModel.getDimType().equals("1")) {
                    if (null == keys) {
                        keys = "`" + key + "`";
                    } else {
                        keys = keys + ",`" + key + "`";
                    }
                }
                if (columnInfoModel.getDimType().equals("3")) {

                    if (indexKeys != null) {
                        indexKeys = indexKeys + ",`" + key + "`(60)";
                    } else {
                        indexKeys = "`" + key + "`(60)";
                    }
                }
                String columnType = columnInfoModel.getColumnType();
                if (columnType.toLowerCase().contains("varchar")) {
                    mysqlStrCre += "`" + key + "`" + " " + columnInfoModel.getColumnType() + " DEFAULT '' COMMENT '"
                            + columnInfoModel.getColumnValue() + "',";
                } else {
                    mysqlStrCre += "`" + key + "`" + " " + columnInfoModel.getColumnType() + " DEFAULT '0' COMMENT '"
                            + columnInfoModel.getColumnValue() + "',";
                }
            }
            // 如果报表存在主键则拼接主键,如果不存在则去掉前面多拼接的逗号
            if (ToolUtils.isNotNull(keys)) {
                mysqlStrCre += "PRIMARY KEY (`id`),UNIQUE INDEX `Index_ymd_part_cid` (" + keys + ") USING BTREE";
            } else {
                mysqlStrCre += "PRIMARY KEY (`id`)";
            }
            if (ToolUtils.isNotNull(indexKeys)) {
                mysqlStrCre += ",INDEX `No_unique_index` (" + indexKeys + ") USING BTREE";
            }
            mysqlStrCre += ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ";
            mysqlStrCre += "COMMENT='" + crm.getApplyname() + "'";

            statement.close();

            logger.info("创建结果表 " + crm.getApplysqlname() + " 创建Mysql结果表: " + mysqlStrCre);
            statement = mysqlConnCre.prepareStatement(mysqlStrCre);
            statement.execute(mysqlStrCre);

            return "0" + Constant.ERR_MSG_SPLIT_FLAG + "创建报表结果表成功";
        } catch (Exception e) {
            logger.info("创建结果表 " + crm.getApplysqlname() + " 创建结果表失败,", e);
            return "-1" + Constant.ERR_MSG_SPLIT_FLAG + "创建结果表失败," + e.getMessage();
        } finally {
            DbOperationUtil.cleanConnEnv(mysqlConnCre, statement);
        }
    }

    public void setConfDataSource(DataSource confDataSource) {
        this.confDataSource = confDataSource;
    }

    public void setCusMysqlDataSource(DataSource cusMysqlDataSource) {
        this.cusMysqlDataSource = cusMysqlDataSource;
    }
}
